package ict.kth.se.model.repository.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import ict.kth.se.exceptions.EntityAlreadyExistsInDBException;
import ict.kth.se.exceptions.EntityDoesntExistInDBException;
import ict.kth.se.model.Availability;
import ict.kth.se.model.repository.AvailabilityRepository;

public class JdbcAvailabilityRepository implements AvailabilityRepository {

	private JdbcTemplate jdbcTemplate;
	private RowMapper<Availability> rowMapper = new AvailabilityRowMapper();
	
	/**
	 * Public constructor
	 * @param dataSource datasource to create jdbctemplate
	 */
	public JdbcAvailabilityRepository(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	/**
	 * Get Availability by id
	 * @param id Id of availability
	 * @return Found availability
	 */
	@Override
	public Availability getAvailabilityById(Long id) throws SQLException{
		try{
			String sql = "SELECT * FROM Availability WHERE availability_id = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, id);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get Availabilities by person's id
	 * @param id Id of person to get availabilities
	 * @return Found availabilities
	 */
	@Override
	public List<Availability> getAvailabilitiesByPersonId(Long personId) throws SQLException{
		try{
			String sql = "SELECT * FROM Availability WHERE person_id = ?;";
			return jdbcTemplate.query(sql, rowMapper, personId);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get all availabilities
	 * @return Found availabilities
	 */
	@Override
	public List<Availability> getAllAvailabilities() throws SQLException{
		try{
			String sql = "SELECT * FROM Availability;";
			return jdbcTemplate.query(sql, rowMapper);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method removes one availability from database (by it's ID)
	 * @param availability The availability to be removed
	 * @throws EntityDoesntExistInDBException If availability doesn't exist in database
	 * @throws SQLException
	 */
	@Override
	public void removeAvailability(Availability availability) throws EntityDoesntExistInDBException, SQLException{
		if(this.getAvailabilityById(availability.getId()) == null)
			throw new EntityDoesntExistInDBException("No such availability in database");
		
		String sql = "DELETE FROM Availability WHERE availability_id = ?;";
		jdbcTemplate.update(sql, availability.getId());
	}
	
	/**
	 * This method removes one availability from database by person's ID
	 * @param personId ID of the person to delete availability for
	 * @throws EntityDoesntExistInDBException If availability doesn't exist in database
	 * @throws SQLException
	 */
	@Override
	public void removeAvailabilitiesForPerson(Long personId) throws EntityDoesntExistInDBException, SQLException{
		String sql = "DELETE FROM Availability WHERE person_id = ?;";
		jdbcTemplate.update(sql, personId);
	}
	
	/**
	 * This method adds an availability in DB
	 * @param availability Availability to be added
	 * @param personId Id of person that availability should be connected to
	 * @return Newly created availability
	 * @throws EntityAlreadyExistsInDBException If such availability already exists in DB (by name)
	 * @throws SQLException
	 */
	@Override
	public Availability createAvailability(Availability availability, Long personId) throws EntityAlreadyExistsInDBException, SQLException{
		//Some controll before insertion
		if(availability == null)
			throw new SQLException("Availability can't be null");
		
		if(availability.getFromDate() == null || availability.getToDate() == null)
			throw new SQLException("Corrupted date parameters.");
		
		//If not than add it
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final Date fromDate = new Date(availability.getFromDate().getTimeInMillis());
		final Date toDate = new Date(availability.getToDate().getTimeInMillis());
		final Long personIdFinal = personId;
		
		final String sql = "INSERT INTO Availability (from_date, to_date, person_id) VALUES (?, ?, ?);";
		
		jdbcTemplate.update(
			    new PreparedStatementCreator() {
			        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"availability_id"});
			            ps.setDate(1, fromDate);
			            ps.setDate(2, toDate);
			            ps.setLong(3, personIdFinal);
			            return ps;
			        }
			    },
			    keyHolder);
		
		//After update add new id and return
		availability.setId(keyHolder.getKey().longValue());
		return availability;
	}
	
	/**
	 * This method updates an availability in DB
	 * @param availability Availability to be updated
	 * @param personId Id of person that availability should be connected to
	 * @throws EntityDoesntExistInDBException If such availability doesn't exist in DB (by id)
	 * @throws SQLException
	 */
	@Override
	public void updateAvailability(Availability availability, Long personId) throws EntityDoesntExistInDBException, SQLException{
		
		//Some controll before updating
		if(availability == null)
			throw new SQLException("Availability can't be null.");
		
		if(personId == null)
			throw new SQLException("Person's ID can't be null.");
		
		if(this.getAvailabilityById(availability.getId()) == null)
			throw new EntityDoesntExistInDBException("No such availability in database");
		
		if(availability.getFromDate() == null || availability.getToDate() == null)
			throw new SQLException("Corrupted input parameters.");
		
		String sql = "UPDATE Availability SET from_date = ?, to_date = ?, person_id = ? " +
			"WHERE availability_id = ?;";
		
		jdbcTemplate.update(sql, new Date(availability.getFromDate().getTimeInMillis()),
				new Date(availability.getToDate().getTimeInMillis()), personId, availability.getId());
	}

	/**
	 * RowMapper to map vailability objects 
	 */
	class AvailabilityRowMapper implements RowMapper<Availability>{
		public Availability mapRow(ResultSet rs, int rowNum) throws SQLException{
			return mapAvailability(rs);
		}
	}
	
	/**
	 * Maps a row returned from a query of Availability to a Availability object.
	 * @param rs the result set with its cursor positioned at the current row
	 */
	private Availability mapAvailability(ResultSet rs) throws SQLException {
		Availability availability = new Availability();
		availability.setId(rs.getLong("availability_id"));
		//Date to Calendar
		Calendar cal1 = Calendar.getInstance();
		cal1.setTimeInMillis(rs.getDate("from_date").getTime());
		availability.setFromDate(cal1);
		Calendar cal2 = Calendar.getInstance();
		cal2.setTimeInMillis(rs.getDate("to_date").getTime());
		availability.setToDate(cal2);
		
		return availability;
	}
}
